<?php

class StudentQuery_NumberController extends Zend_Controller_Action
{

    public function init()
    {
        /* Initialize action controller here */
    }

    public function indexAction()
    {
        $year = $this->view->Semester['year'];
        $semester = $this->view->Semester['semester'];

        $q  = Doctrine_Query::create()
        ->select('a.grade, a.class_sn, b.sex AS sex,  COUNT(a.sn) cc ')
        ->from('Student_Model_StudentSemester a')
        ->leftJoin('a.Student_Model_Student b')
        ->whereIN('b.study_condition',
        array(Student_Model_StudentTransfer::IN_STADY,
        Student_Model_StudentTransfer::HOMESCHOOLING))
        ->andWhere('a.year=?', $year)
        ->andWhere('a.semester=?', $semester)
        ->groupBy('a.grade, a.class_sn, b.sex');

        $arr = array();
        $gradeArr = array();
        foreach($q->fetchArray() as $row) {
            if (! isset($gradeArr[$row['grade']][$row['sex']]) )
            $gradeArr[$row['grade']][$row['sex']] = 0;
            // 計算各班級人數
            $arr[$row['grade'].'-'.$row['class_sn']][$row['sex']] = $row['cc'];
            // 計算各年級男女生數
            $gradeArr[$row['grade']][$row['sex']] += $row['cc'];
            // 計算班級數
            $gradeArr[$row['grade']]['class'][$row['grade'].'-'.$row['class_sn']] = 1;
        }

        $this->view->data = $arr;
        $this->view->gradeData = $gradeArr;

        $semesterClass = new Semester_Model_SemesterClass();
        $this->view->className = $semesterClass->getClassNames($year,$semester);

        // 查詢在家自學學生
        $q = Doctrine_Query::create()
        ->select('a.name, a.sex,  b.grade AS grade, b.class_sn AS class_sn , b.number AS number')
        ->from('Student_Model_Student a')
        ->leftJoin('a.Student_Model_StudentSemester b')
        ->where('b.year=?', $year)
        ->andWhere('b.semester=?', $semester)
        ->andWhere('a.study_condition=?', Student_Model_StudentTransfer::HOMESCHOOLING);
        $this->view->homeSchooling = $q->fetchArray();

    }


}

